


import os, _pg
import mailbox, project



def setupTables(db):
    q = """
CREATE TABLE People (
	PersonID	serial		NOT NULL,
	name		varchar(200),
	isDev		boolean default false,
	hasEmail	boolean default false,
PRIMARY KEY (PersonID)
);

CREATE TABLE Aliases (
    AliasID     serial NOT NULL,
    email       varchar(200) NOT NULL unique,
    name        varchar(200) NOT NULL,
    PersonID    int references People(PersonID),
PRIMARY KEY (AliasID)
);

CREATE TABLE Messages(
    MessageID   varchar(200)            NOT NULL,
    SenderAliasID   int                 references Aliases(AliasID) NOT NULL,
    ReferenceID varchar(200)            references Messages(MessageID) default NULL,
    RecipAliasID    int             references Aliases(AliasID) default NULL,
    DateTime    timestamp(2) with time zone     NOT NULL,
    Subject     varchar(512)            NOT NULL,
    Body        text                NOT NULL,
    List        varchar(256)        NOT NULL,
PRIMARY KEY  (MessageID)
);

CREATE INDEX SenderAliasID_idx ON Messages(SenderAliasID);
CREATE INDEX RecipAliasID_idx ON Messages(RecipAliasID);
CREATE INDEX ReferenceID_idx ON Messages(ReferenceID);   
"""
    dropq = """
drop table Messages cascade;
drop table Aliases cascade;
drop table People cascade;
"""
    try:
        print "cleaning Email tables..."
        db.query(dropq)
        print "...OK!"
    except _pg.ProgrammingError:
            print "could not drop tables"
    try:
        print "creating Email tables..."
        db.query(q)
        print "...OK!"
    except _pg.ProgrammingError, e:
        print "could not create tables"
        raise project.ProjectError("Could not create tables, cause:" + str(e))

def start(proj, log):
    mailDir = proj.projectDir + proj['mailDir']
    for mailinglist in os.listdir(mailDir):
        dir = mailDir + "/" + mailinglist
        db = proj.getDatabase()
        setupTables(db)
        log.write("setup clean messages and aliases tables")
        mailbox.parseMbox(db, dir, mailinglist)
        log.write("parsed " + mailinglist + " into database.")
